#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
PRESTO_HOME=/export/server/presto/bin/presto

${HIVE_HOME} -S -e "
-- 建库
-- 创建DWD层
Drop DataBase If Exists zx_dwd;
Create DataBase If Not Exists zx_dwd;

-- 切换库
Use zx_dwd;

-- todo 学生打卡信息表 tbh_student_signin_record
Drop Table If Exists zx_dwd.fact_tbh_student_signin_record;
Create Table If Not Exists zx_dwd.fact_tbh_student_signin_record(
    id                Int      Comment '主键id',
    normal_class_flag Int      Comment '是否正课 1 正课 2 自习',
    time_table_id     Int      Comment '作息时间id 关联tbh_school_time_table 或者 tbh_class_time_table',
    class_id          Int      Comment '班级id',
    student_id        Int      Comment '学员id',
    signin_time       String   Comment '签到时间',
    signin_date       String   Comment '签到日期',
    inner_flag        Int      Comment '内外网标志  0 外网 1 内网',
    signin_type       Int      Comment '签到类型 1 心跳打卡 2 老师补卡',
    share_state       Int      Comment '共享屏幕状态 0 否 1是  在上午或下午段有共屏记录，则该段所有记录该字段为1，内网默认为1 外网默认为0 ',
    inner_ip          String   Comment '内网ip地址'
) Comment '学生打卡信息表'
Row Format Delimited Fields Terminated By '\t'
Stored As ORC Tblproperties ('orc.compress' = 'SNAPPY');

-- todo 班级作息时间表 tbh_class_time_table
Drop Table If Exists zx_dwd.dim_tbh_class_time_table;
Create Table If Not Exists zx_dwd.dim_tbh_class_time_table(
    id                    Int      Comment '主键id',
    class_id              Int      Comment '班级id',
    morning_template_id   Int      Comment '上午出勤模板id',
    morning_begin_time    String   Comment '上午开始时间',
    morning_end_time      String   Comment '上午结束时间',
    afternoon_template_id Int      Comment '下午出勤模板id',
    afternoon_begin_time  String   Comment '下午开始时间',
    afternoon_end_time    String   Comment '下午结束时间',
    evening_template_id   Int      Comment '晚上出勤模板id',
    evening_begin_time    String   Comment '晚上开始时间',
    evening_end_time      String   Comment '晚上结束时间',
    use_begin_date        String   Comment '使用开始日期',
    use_end_date          String   Comment '使用结束日期',
    create_time           String   Comment '创建时间',
    create_person         Int      Comment '创建人',
    remark                String   Comment '备注'
) Comment '班级作息时间表'
Row Format Delimited Fields Terminated By '\t'
Stored As ORC Tblproperties ('orc.compress' = 'SNAPPY');

-- todo 班级排课信息表 course_table_upload_detail
Drop Table If Exists zx_dwd.dim_course_table_upload_detail;
Create Table If Not Exists zx_dwd.dim_course_table_upload_detail(
    id                  Int     Comment 'id',
    base_id             Int     Comment '课程主表id',
    class_id            Int     Comment '班级id',
    class_date          String  Comment '上课日期',
    content             String  Comment '课程内容',
    teacher_id          Int     Comment '老师id',
    teacher_name        String  Comment '老师名字',
    job_number          String  Comment '工号',
    classroom_id        Int     Comment '教室id',
    classroom_name      String  Comment '教室名称',
    is_outline          Int     Comment '是否大纲 0 否 1 是',
    class_mode          Int     Comment '上课模式 0 传统全天 1 AB上午 2 AB下午 3 线上直播',
    is_stage_exam       Int     Comment '是否阶段考试（0：否 1：是）',
    is_pay              Int     Comment '代课费（0：无 1：有）',
    tutor_teacher_id    Int     Comment '晚自习辅导老师id',
    tutor_teacher_name  String  Comment '辅导老师姓名',
    tutor_job_number    String  Comment '晚自习辅导老师工号',
    is_subsidy          Int     Comment '晚自习补贴（0：无 1：有）',
    answer_teacher_id   Int     Comment '答疑老师id',
    answer_teacher_name String  Comment '答疑老师姓名',
    answer_job_number   String  Comment '答疑老师工号',
    remark              String  Comment '备注',
    create_time         String  Comment '创建时间'
) Comment '班级排课信息表'
Row Format Delimited Fields Terminated By '\t'
Stored As ORC Tblproperties ('orc.compress' = 'SNAPPY');

-- todo 在读学员人数信息表 class_studying_student_count
Drop Table If Exists zx_dwd.fact_class_studying_student_count;
Create Table If Not Exists zx_dwd.fact_class_studying_student_count(
    id                     Int ,
    school_id              Int    Comment '校区id',
    subject_id             Int    Comment '学科id',
    class_id               Int    Comment '班级id',
    studying_student_count Int    Comment '在读班级人数',
    studying_date          String Comment '在读日期'
) comment '在读学员人数信息表'
Row Format Delimited Fields Terminated By '\t'
Stored As ORC Tblproperties ('orc.compress' = 'SNAPPY');

-- todo 学生请假申请表 student_leave_apply
Drop Table If Exists zx_dwd.fact_student_leave_apply;
Create Table If Not Exists zx_dwd.fact_student_leave_apply(
    id              Int ,
    class_id        Int     Comment '班级id',
    student_id      Int     Comment '学员id',
    audit_state     Int     Comment '审核状态 0 待审核 1 通过 2 不通过',
    audit_person    Int     Comment '审核人',
    audit_time      String  Comment '审核时间',
    audit_remark    String  Comment '审核备注',
    leave_type      Int     Comment '请假类型  1 请假 2 销假',
    leave_reason    Int     Comment '请假原因  1 事假 2 病假',
    begin_time      String  Comment '请假开始时间',
    begin_time_type Int     Comment '1：上午 2：下午',
    end_time        String  Comment '请假结束时间',
    end_time_type   Int     Comment '1：上午 2：下午',
    days            String  Comment '请假/已休天数',
    cancel_state    Int     Comment '撤销状态  0 未撤销 1 已撤销',
    cancel_time     String  Comment '撤销时间',
    old_leave_id    Int     Comment '原请假id，只有leave_type =2 销假的时候才有',
    leave_remark    String  Comment '请假/销假说明',
    valid_state     Int     Comment '是否有效（0：无效 1：有效）',
    create_time     String  Comment '创建时间'
) comment '学生请假申请表'
Row Format Delimited Fields Terminated By '\t'
Stored As ORC Tblproperties ('orc.compress' = 'SNAPPY');

"

wait 3

${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "

Use hive.zx_dwd;

-- todo 学生打卡信息表 tbh_student_signin_record
Insert Into hive.zx_dwd.fact_tbh_student_signin_record
Select
    id,                                             -- '主键id'
    normal_class_flag,                              -- '是否正课 1 正课 2 自习'
    time_table_id,                                  -- '作息时间id 关联tbh_school_time_table 或者 tbh_class_time_table'
    class_id,                                       -- '班级id'
    student_id,                                     -- '学员id'
    Substring(signin_time, 1, 19) As signin_time,   -- '签到时间'
    signin_date,                                    -- '签到日期'
    inner_flag,                                     -- '内外网标志  0 外网 1 内网'
    signin_type,                                    -- '签到类型 1 心跳打卡 2 老师补卡'
    share_state,                                    -- '共享屏幕状态 0 否 1是  在上午或下午段有共屏记录，则该段所有记录该字段为1，内网默认为1 外网默认为0 '
    inner_ip                                        -- '内网ip地址'
From hive.zx_ods.tbh_student_signin_record;


-- todo 班级作息时间表 tbh_class_time_table
Insert Into hive.zx_dwd.dim_tbh_class_time_table
Select
    id,
    class_id,
    morning_template_id,
    morning_begin_time,
    morning_end_time,
    afternoon_template_id,
    afternoon_begin_time,
    afternoon_end_time,
    evening_template_id,
    evening_begin_time,
    evening_end_time,
    use_begin_date,
    use_end_date,
    Substring(create_time, 1, 19) As create_time,
    create_person,
    remark
From hive.zx_ods.tbh_class_time_table;

-- todo 班级排课信息表 course_table_upload_detail
Insert Into hive.zx_dwd.dim_course_table_upload_detail
Select id,
       base_id,
       class_id,
       class_date,
       content,
       teacher_id,
       teacher_name,
       job_number,
       classroom_id,
       classroom_name,
       is_outline,
       class_mode,
       is_stage_exam,
       is_pay,
       tutor_teacher_id,
       tutor_teacher_name,
       tutor_job_number,
       is_subsidy,
       answer_teacher_id,
       answer_teacher_name,
       answer_job_number,
       remark,
       create_time
From hive.zx_ods.course_table_upload_detail
Where content Is Not Null And content <> '' And content <> '开班典礼';


-- todo 在读学员人数信息表 class_studying_student_count
Insert Into hive.zx_dwd.fact_class_studying_student_count
Select id,
       school_id,
       subject_id,
       class_id,
       studying_student_count,
       studying_date
From hive.zx_ods.class_studying_student_count;


-- todo 学生请假申请表 student_leave_apply
Insert Into hive.zx_dwd.fact_student_leave_apply
select id,
       class_id,
       student_id,
       audit_state,
       audit_person,
       Substring(audit_time, 1, 19) As audit_time,
       audit_remark,
       leave_type,
       leave_reason,
       Substring(begin_time, 1, 19) As begin_time,
       begin_time_type,
       Substring(end_time, 1, 19) As end_time,
       end_time_type,
       days,
       cancel_state,
       Substring(cancel_time, 1, 19) As cancel_time,
       old_leave_id,
       leave_remark,
       valid_state,
       Substring(create_time, 1, 19) As create_time
from hive.zx_ods.student_leave_apply
Where audit_state = 1 And valid_state = 1;

"

